const Service = require('egg').Service;
 const lodash = require('lodash');

class SysTestService extends Service {
    /**
     * 新增
     * @param {提交的数据} data 
     * @return 1，0
     */
    async create(data) {
        const { ctx,app } = this;
            let rs = await ctx.model.SysTest.create(data);
    }

    /**
     * 编辑
     * @param {提交的数据} data 
     * @return 1，0
     */
    async update(data) {
        const { ctx,app } = this;
        let rs = await ctx.model.SysTest.update(data);
    }

    /**
     * 查询列表
     * @param {提交的数据} data 
     * @return 1，0
     */
    async querylist(queryParams,pageSize,page) {
        const { ctx,app } = this;
        queryParams = queryParams || {};
        //方便更改,使用 sql 查询,以下步骤为生产sql的过程
        let whereSQL = "where";
    let whereParams = {};
if (!app.utils.validator.isEmpty(queryParams['username'])) {
            whereParams['username'] = queryParams['username'];
            if (whereSQL == "where") {
                whereSQL = whereSQL + ' t.username Like :username';
            } else {
                whereSQL = whereSQL + ' and t.username Like :username';
            }
        }
if (!app.utils.validator.isEmpty(queryParams['name'])) {
            whereParams['name'] = queryParams['name'];
            if (whereSQL == "where") {
                whereSQL = whereSQL + ' t.name Like :name';
            } else {
                whereSQL = whereSQL + ' and t.name Like :name';
            }
        }
if (!app.utils.validator.isEmpty(queryParams['phone'])) {
            whereParams['phone'] = queryParams['phone'];
            if (whereSQL == "where") {
                whereSQL = whereSQL + ' t.phone Like :phone';
            } else {
                whereSQL = whereSQL + ' and t.phone Like :phone';
            }
        }
if(whereSQL == "where"){
        whereSQL = ""
    }
    //增加页码
    page = (page || 1) * 1;
    pageSize = (pageSize || 10) * 1;
    let dataWhereParams = { ...whereParams };
    dataWhereParams.offset = (page - 1) * pageSize;
    dataWhereParams.limit = pageSize;
    let dataSql = "SELECT * FROM sys_test t " + whereSQL + " LIMIT :offset, :limit";
    let countSql = "SELECT count(*) as total FROM sys_test t " + whereSQL;
        let datalist = await ctx.model.query(dataSql,
        { replacements: dataWhereParams, raw: true, type: app.Sequelize.QueryTypes.SELECT });
        //查询总数
        let count = await ctx.model.query(countSql,
        { replacements: whereParams, raw: true, type: app.Sequelize.QueryTypes.SELECT });
        return {
            datalist,
            totalSize:count[0].total
        }
    }

    /**
     * 删除
     * @param {提交的数据} data 
     * @return 1，0
     */
    async delete(id) {
        const { ctx,app } = this;
        let rs = await ctx.model.SysTest.destroy({"where":{id:id}});
    }

    /**
     * 查询详情
     * @param {提交的数据} data 
     * @return 1，0
     */
    async queryById(id) {
        const { ctx,app } = this;
        let rs = await ctx.model.SysTest.findOne({id:id});
        return rs;
    }
}

module.exports = SysTestService;